using Sybase.Data.AseClient;
using System;
using System.Data;

namespace Glpt.Data
{
    /// <summary>
    /// SybaseASE的数据库连接类
    /// </summary>
    public class ASEInterFace : AbstractInterFace
    {
        private AseConnection connsql = null;
        private AseTransaction tran = null;
        private DataTable schemaTable = null;
        /// <summary>
        /// 初始化类的新实例
        /// </summary>
        /// <param name="dbitem">数据库项目</param>
        public ASEInterFace(DbItem dbitem)
        {
            connsql = new AseConnection(ReplaceString(dbitem.GetConnString()));
        }

        /// <summary>
        /// 描述列元数据的DataTable
        /// </summary>
        public override DataTable SchemaTable
        {
            get
            {
                return schemaTable;
            }
        }

        /// <summary>
        /// 参数字符标志
        /// </summary>
        public override string ParamChar
        {
            get
            {
                return ":";
            }
        }

        /// <summary>
        /// 函数字符标志
        /// </summary>
        public override string FunctionChar
        {
            get
            {
                return "";
            }
        }

        /// <summary>
        /// 字符串连接标志
        /// </summary>
        public override string ConcatChar
        {
            get
            {
                return "+";
            }
        }

        /// <summary>
        /// 字段左字符
        /// </summary>
        public override string ColumnLeftChar
        {
            get
            {
                return "[";
            }
        }

        /// <summary>
        /// 字段右字符
        /// </summary>
        public override string ColumnRightChar
        {
            get
            {
                return "]";
            }
        }

        /// <summary>
        /// 获取SQL参数
        /// </summary>
        /// <param name="db_params">参数</param>
        /// <returns>SQL参数</returns>
        private AseParameter GetSqlParameter(DbParam db_params)
        {
            AseParameter sql_params = new AseParameter();
            sql_params.ParameterName = db_params.ParameterName;
            if (db_params.Value == null || db_params.Value is System.DBNull)
                sql_params.Value = DBNull.Value;
            else
                sql_params.Value = db_params.Value;
            sql_params.Direction = db_params.Direction;
            if (db_params.DataType.Equals(DbParamType.Int))
                sql_params.AseDbType = AseDbType.Integer;
            else if (db_params.DataType.Equals(DbParamType.Double))
                sql_params.AseDbType = AseDbType.Double;
            else if (db_params.DataType.Equals(DbParamType.NVarChar))
                sql_params.AseDbType = AseDbType.NVarChar;
            else if (db_params.DataType.Equals(DbParamType.Char))
                sql_params.AseDbType = AseDbType.Char;
            else if (db_params.DataType.Equals(DbParamType.DateTime))
                sql_params.AseDbType = AseDbType.DateTime;
            else if (db_params.DataType.Equals(DbParamType.Blob))
                sql_params.AseDbType = AseDbType.Image;
            else
                sql_params.AseDbType = AseDbType.VarChar;
            if (sql_params.Direction.Equals(ParameterDirection.Output)) sql_params.Size = 1024;
            return sql_params;
        }
        /// <summary>
        /// 获取SQL参数列表
        /// </summary>
        /// <param name="db_params">参数列表</param>
        /// <returns>SQL参数列表</returns>
        private AseParameter[] GetSqlParameter(DbParamCollection db_params)
        {
            AseParameter[] sql_params = new AseParameter[db_params.Count];
            for (int i = 0; i < db_params.Count; i++)
            {
                sql_params[i] = GetSqlParameter(db_params[i]);
            }
            return sql_params;
        }

        /// <summary>
        /// 是否有数据库事务
        /// </summary>
        /// <returns>是否</returns>
        public override bool HasTransaction()
        {
            return (tran != null);
        }

        /// <summary>
        /// 开始数据库事务
        /// </summary>
        /// <returns>是否成功</returns>
        public override bool BeginTransaction()
        {
            if (tran == null)
            {
                if (connsql.State.Equals(ConnectionState.Closed)) connsql.Open();
                tran = connsql.BeginTransaction();
                return true;
            }
            else
                return false;
        }

        /// <summary>
        /// 提交数据库事务
        /// </summary>
        public override void Commit()
        {
            try
            {
                if (tran != null)
                    tran.Commit();
            }
            finally
            {
                if (connsql != null && !connsql.State.Equals(ConnectionState.Closed)) connsql.Close();
                tran = null;
            }
        }

        /// <summary>
        /// 从挂起状态回滚事务
        /// </summary>
        public override void Rollback()
        {
            try
            {
                if (tran != null)
                    tran.Rollback();
            }
            finally
            {
                if (connsql != null && !connsql.State.Equals(ConnectionState.Closed)) connsql.Close();
                tran = null;
            }
        }

        /// <summary>
        /// 执行SQL查询
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>结果集</returns>
        public override DataTable ExecSelect(string sql)
        {
            return ExecSelect(null, sql, null, false, -1);
        }

        /// <summary>
        /// 执行SQL查询
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="sql_params">SQL参数列表</param>
        /// <returns>结果集</returns>
        public override DataTable ExecSelect(string sql, DbParamCollection sql_params)
        {
            return ExecSelect(null, sql, sql_params, false, -1);
        }

        /// <summary>
        /// 执行SQL查询
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="sql_params">SQL参数列表</param>
        /// <param name="is_pro">是否是存储过程</param>
        /// <returns>结果集</returns>
        public override DataTable ExecSelect(string sql, DbParamCollection sql_params, bool is_pro)
        {
            return ExecSelect(null, sql, sql_params, is_pro, -1);
        }

        /// <summary>
        /// 执行SQL查询
        /// </summary>
        /// <param name="searchHostObj">发起执行的主体对象</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="sql_params">SQL参数列表</param>
        /// <param name="is_pro">是否是存储过程</param>
        /// <param name="pageIndex">页码，从0开始，-1表示全部</param>
        /// <returns>结果集</returns>
        public override DataTable ExecSelect(object searchHostObj, string sql, DbParamCollection sql_params, bool is_pro, int pageIndex)
        {
            DataTable dt = new DataTable();
            try
            {
                if (connsql.State.Equals(ConnectionState.Closed)) connsql.Open();
                AseCommand cmdsql = new AseCommand(sql, connsql);
                if (tran != null) cmdsql.Transaction = tran;
                cmdsql.CommandTimeout = this.CommandTimeout;
                if (is_pro) cmdsql.CommandType = CommandType.StoredProcedure;
                if (sql_params != null)
                    cmdsql.Parameters.AddRange(GetSqlParameter(sql_params));
                WriteLog(cmdsql);
                AddCommand(searchHostObj, cmdsql, dt);
                try
                {
                    if ((ParamSet.PageRecordCout == 0 || pageIndex < 0) && searchHostObj == null)
                    {
                        using (AseDataAdapter sqlada = new AseDataAdapter(cmdsql))
                        {
                            sqlada.Fill(dt);
                        }
                    }
                    else
                    {
                        //using (SqlDataAdapter sqlada = new SqlDataAdapter(cmdsql))
                        //{
                        //    sqlada.Fill(pageIndex * ParamSet.PageRecordCout, ParamSet.PageRecordCout, dt);
                        //}
                        using (AseDataReader dataReader = cmdsql.ExecuteReader())
                        {
                            try
                            {
                                if (searchHostObj != null)
                                    schemaTable = dataReader.GetSchemaTable();
                                string column_name = "";
                                for (int i = 0; i < dataReader.FieldCount; i++)
                                {
                                    column_name = dataReader.GetName(i);
                                    if (dt.Columns.Contains(column_name))
                                    {
                                        int iseq = 1;
                                        while (dt.Columns.Contains(column_name + iseq.ToString()))
                                        {
                                            iseq++;
                                        }
                                        column_name = column_name + iseq.ToString();
                                    }
                                    DataColumn dc = new DataColumn();
                                    dc.DataType = dataReader.GetFieldType(i);
                                    dc.ColumnName = column_name;
                                    dt.Columns.Add(dc);
                                }
                                int irows = 0;
                                int page_size = ParamSet.PageRecordCout;
                                while (dataReader.Read())
                                {
                                    if (pageIndex >= 0 && irows < pageIndex * page_size)
                                    {
                                        irows++;
                                        continue;
                                    }
                                    else if (pageIndex >= 0 && irows >= (pageIndex + 1) * page_size)
                                    {
                                        cmdsql.Cancel();
                                        break;
                                    }
                                    else
                                    {
                                        irows++;
                                        DataRow dr = dt.NewRow();
                                        for (int i = 0; i < dataReader.FieldCount; i++)
                                        {
                                            dr[i] = dataReader.GetValue(i);
                                        }
                                        dt.Rows.Add(dr);
                                    }
                                }
                                dt.AcceptChanges();
                            }
                            finally
                            {
                                dataReader.Close();
                            }
                        }
                    }
                }
                catch (AseException ex)
                {
                    if (ex.HResult != 0)
                        throw;
                }
                foreach (AseParameter sqlp in cmdsql.Parameters)
                {
                    if (!sqlp.Direction.Equals(ParameterDirection.Input))
                    {
                        DbParam dbp = sql_params[sqlp.ParameterName];
                        if (dbp != null && sqlp.Value != null && !(sqlp.Value is System.DBNull))
                        {
                            if (dbp.DataType.Equals(DbParamType.Int))
                                dbp.Value = int.Parse(sqlp.Value.ToString());
                            else if (dbp.DataType.Equals(DbParamType.Double))
                                dbp.Value = double.Parse(sqlp.Value.ToString());
                            else if (dbp.DataType.Equals(DbParamType.DateTime))
                                dbp.Value = (DateTime)sqlp.Value;
                            else if (dbp.DataType.Equals(DbParamType.Blob))
                                dbp.Value = (byte[])sqlp.Value;
                            else
                                dbp.Value = sqlp.Value.ToString();
                        }
                    }
                }
            }
            finally
            {
                RemoveCommand(searchHostObj);
                if (tran == null && connsql != null && !connsql.State.Equals(ConnectionState.Closed)) connsql.Close();
            }
            return dt;
        }

        /// <summary>
        /// 批量加载数据
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="map_mode">字段映射 0按名称 1按顺序</param>
        /// <param name="reader">数据流</param>
        /// <param name="batchsize">每一批次中的行数</param>
        public override void BulkCopy(string tablename, string map_mode, IDataReader reader, int batchsize)
        {
            try
            {
                if (connsql.State.Equals(ConnectionState.Closed)) connsql.Open();
                using (AseBulkCopy sqlbulkcopy = new AseBulkCopy(connsql, AseBulkCopyOptions.UseInternalTransaction, null))
                {
                    sqlbulkcopy.DestinationTableName = tablename;//表名
                    SqlBulkCopyColumnMap(sqlbulkcopy, map_mode);//字段映射
                    sqlbulkcopy.BulkCopyTimeout = 7200;//超时秒数
                    sqlbulkcopy.BatchSize = batchsize;//每批记录数
                    sqlbulkcopy.WriteToServer(reader);//批复制
                }
            }
            finally
            {
                if (tran == null && connsql != null && !connsql.State.Equals(ConnectionState.Closed)) connsql.Close();
            }
        }

        /// <summary>
        /// 批量加载数据
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="map_mode">字段映射 0按名称 1按顺序</param>
        /// <param name="table">数据表</param>
        /// <param name="batchsize">每一批次中的行数</param>
        public override void BulkCopy(string tablename, string map_mode, DataTable table, int batchsize)
        {
            try
            {
                if (table.Rows.Count == 0) return;
                if (connsql.State.Equals(ConnectionState.Closed)) connsql.Open();
                using (AseBulkCopy sqlbulkcopy = new AseBulkCopy(connsql, AseBulkCopyOptions.UseInternalTransaction, null))
                {
                    sqlbulkcopy.DestinationTableName = tablename;//表名
                    SqlBulkCopyColumnMap(sqlbulkcopy, map_mode);//字段映射
                    sqlbulkcopy.BulkCopyTimeout = 7200;//超时秒数
                    sqlbulkcopy.BatchSize = batchsize;//每批记录数
                    sqlbulkcopy.WriteToServer(table);//批复制
                }
            }
            finally
            {
                if (tran == null && connsql != null && !connsql.State.Equals(ConnectionState.Closed)) connsql.Close();
            }
        }

        /// <summary>
        /// 批量加载数据
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="map_mode">字段映射 0按名称 1按顺序</param>
        /// <param name="rows">数据行</param>
        /// <param name="batchsize">每一批次中的行数</param>
        public override void BulkCopy(string tablename, string map_mode, DataRow[] rows, int batchsize)
        {
            try
            {
                if (rows.Length == 0) return;
                if (connsql.State.Equals(ConnectionState.Closed)) connsql.Open();
                using (AseBulkCopy sqlbulkcopy = new AseBulkCopy(connsql, AseBulkCopyOptions.UseInternalTransaction, null))
                {
                    sqlbulkcopy.DestinationTableName = tablename;//表名
                    SqlBulkCopyColumnMap(sqlbulkcopy, map_mode);//字段映射
                    sqlbulkcopy.BulkCopyTimeout = 7200;//超时秒数
                    sqlbulkcopy.BatchSize = batchsize;//每批记录数
                    sqlbulkcopy.WriteToServer(rows);//批复制
                }
            }
            finally
            {
                if (tran == null && connsql != null && !connsql.State.Equals(ConnectionState.Closed)) connsql.Close();
            }
        }

        private void SqlBulkCopyColumnMap(AseBulkCopy sqlbulkcopy, string map_mode)
        {
            string[] fields = map_mode.Split(new char[] { ',' });
            for (int i = 1; i < fields.Length; i++)
            {
                string[] vals = fields[i].Split(new char[] { ':' });
                AseBulkCopyColumnMapping mp = null;
                if (fields[0].Equals("1"))
                    mp = new AseBulkCopyColumnMapping(int.Parse(vals[0]), int.Parse(vals[1]));
                else
                    mp = new AseBulkCopyColumnMapping(vals[0], vals[1]);
                sqlbulkcopy.ColumnMappings.Add(mp);
            }
        }

        /// <summary>
        /// 获取数据流
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>数据流</returns>
        public override IDataReader ExecReader(string sql)
        {
            return ExecReader(sql, null, false);
        }

        /// <summary>
        /// 获取数据流
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="sql_params">SQL参数列表</param>
        /// <returns>数据流</returns>
        public override IDataReader ExecReader(string sql, DbParamCollection sql_params)
        {
            return ExecReader(sql, sql_params, false);
        }

        /// <summary>
        /// 获取数据流
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="sql_params">SQL参数列表</param>
        /// <param name="is_pro">是否是存储过程</param>
        /// <returns>数据流</returns>
        public override IDataReader ExecReader(string sql, DbParamCollection sql_params, bool is_pro)
        {
            try
            {
                if (connsql.State.Equals(ConnectionState.Closed)) connsql.Open();
                AseCommand cmdsql = new AseCommand(sql, connsql);
                if (tran != null) cmdsql.Transaction = tran;
                cmdsql.CommandTimeout = this.CommandTimeout;
                if (is_pro) cmdsql.CommandType = CommandType.StoredProcedure;
                if (sql_params != null)
                    cmdsql.Parameters.AddRange(GetSqlParameter(sql_params));
                WriteLog(cmdsql);
                return cmdsql.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                if (tran == null && connsql != null && !connsql.State.Equals(ConnectionState.Closed)) connsql.Close();
                throw;
            }
        }

        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>结果集</returns>
        public override int ExecUpdate(string sql)
        {
            return ExecUpdate(null, sql, null, false);
        }

        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="sql_params">SQL参数列表</param>
        /// <returns>结果集</returns>
        public override int ExecUpdate(string sql, DbParamCollection sql_params)
        {
            return ExecUpdate(null, sql, sql_params, false);
        }

        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="sql_params">SQL参数列表</param>
        /// <param name="is_pro">是否是存储过程</param>
        /// <returns>结果集</returns>
        public override int ExecUpdate(string sql, DbParamCollection sql_params, bool is_pro)
        {
            return ExecUpdate(null, sql, sql_params, is_pro);
        }

        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="searchHostObj">发起执行的主体对象</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="sql_params">SQL参数列表</param>
        /// <param name="is_pro">是否是存储过程</param>
        /// <returns>结果集</returns>
        public override int ExecUpdate(object searchHostObj, string sql, DbParamCollection sql_params, bool is_pro)
        {
            try
            {
                if (connsql.State.Equals(ConnectionState.Closed)) connsql.Open();
                AseCommand cmdsql = new AseCommand(sql, connsql);
                if (tran != null) cmdsql.Transaction = tran;
                cmdsql.CommandTimeout = this.CommandTimeout;
                if (is_pro) cmdsql.CommandType = CommandType.StoredProcedure;
                if (sql_params != null)
                    cmdsql.Parameters.AddRange(GetSqlParameter(sql_params));
                WriteLog(cmdsql);
                AddCommand(searchHostObj, cmdsql);
                int ret = cmdsql.ExecuteNonQuery();
                if (sql_params != null)
                {
                    foreach (AseParameter sqlp in cmdsql.Parameters)
                    {
                        if (!sqlp.Direction.Equals(ParameterDirection.Input))
                        {
                            DbParam dbp = sql_params[sqlp.ParameterName];
                            if (dbp != null && sqlp.Value != null && !(sqlp.Value is System.DBNull))
                            {
                                if (dbp.DataType.Equals(DbParamType.Int))
                                    dbp.Value = int.Parse(sqlp.Value.ToString());
                                else if (dbp.DataType.Equals(DbParamType.Double))
                                    dbp.Value = double.Parse(sqlp.Value.ToString());
                                else if (dbp.DataType.Equals(DbParamType.DateTime))
                                    dbp.Value = (DateTime)sqlp.Value;
                                else if (dbp.DataType.Equals(DbParamType.Blob))
                                    dbp.Value = (byte[])sqlp.Value;
                                else
                                    dbp.Value = sqlp.Value.ToString();
                            }
                        }
                    }
                }
                return ret;
            }
            finally
            {
                RemoveCommand(searchHostObj);
                if (tran == null && connsql != null && !connsql.State.Equals(ConnectionState.Closed)) connsql.Close();
            }
        }

        /// <summary>
        /// 执行与释放或重置非托管资源相关的应用程序定义的任务
        /// </summary>
        public override void Dispose()
        {
            connsql = null;
        }
    }
}
